package cn.yunyichina.provider.base.service.impl;

import java.util.Calendar;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.fy.util.CommonUtil;
import com.fy.util.Record;
import com.fy.util.RecordSet;
import com.fy.util.db.CommonService;

import cn.yunyichina.provider.base.dao.sync.impl.ImportSourceDao;
import cn.yunyichina.provider.iface.entity.ErrorCodeDefine;
import cn.yunyichina.utils.commons.IDUtils;
import cn.yunyichina.utils.commons.StringUtils;
import cn.yunyichina.utils.convert.JsonUtils;

/**
 * 订单表ord_order旧数据库同步到新数据库
 * @author adriftor
 *
 */
@Service("ordersyncServiceImpl")
public class OrderSyncServiceImpl extends CommonService {
	
	private static final Logger logger = LoggerFactory.getLogger(OrderSyncServiceImpl.class);
	/**
	 * 数据来源表
	 */
	private static final String sourceTableName = "v_ord_order_sync";
	
	/**
	 * 数据来源实际表
	 */
//	private static final String sourceRealTableName= "ord_order_sync";
	
	/**
	 * 数据目标表
	 */
	private static final String targetTableName = "ord_order";
	
	
	
	/**
	 * 数据同步时，分页大小
	 */
	private static int pageSize = 300;
	/**
	 * eh_user表查询语句
	 */
	private static final String sqlEhUser;
	
	/**
	 * medical_card查询语句
	 */
	private static final String sqlMedicalCard;
	
	static {
		
		//初始化eh_user表和medical_card表查询语句
		
		String sourceDatabaseName = ImportSourceDao.isRelease() ? "yunyi_pro" : "yunyi_test";
		
		StringBuilder sb = new StringBuilder("select * from (");
		for (int k = 1;k<=10;k++) {
			sb.append("select * from "+sourceDatabaseName+".eh_user_"+k+" where id=:user_id");
			if (k != 10) {
				sb.append(" union ");
			}
			
		}
		sb.append(") t");

		sqlEhUser = sb.toString();
		
		//从诊疗卡表获取用户数据
		sb = new StringBuilder("select * from (");
		for (int k = 1;k<=10;k++) {
			sb.append("select * from "+sourceDatabaseName+".biz_medical_card_"+k+" where user_id=:user_id");
			if (k != 10) {
				sb.append(" union ");
			}
			
		}
		sb.append(") t");
		
		sqlMedicalCard = sb.toString();
	}
	
	public void doOrderSync() {
	
		
		logger.info(sourceTableName+"数据同步线程启动");
		
		Record rdParam = new Record();
		rdParam.put("sync_table",targetTableName);

		Long start = System.currentTimeMillis();
		ImportSourceDao dao = null;
		try {
			logger.debug(sourceTableName+"数据同步开始");
			
			dao = new ImportSourceDao();
			dao.begin();	
			
			String sql;
			
			//获取上次同步截止的最大ID
			sql = "select max(sync_max_id) from sys_sync_id where sync_table=?";
			String maxId = this.queryForString(sql,rdParam);
			if (StringUtils.isEmpty(maxId)) {
				this.update("delete from sys_sync_id where sync_table=?",rdParam);
				this.update("insert sys_sync_id(sync_table,sync_max_id) values(?,'')",rdParam);
				maxId = "";
			}
			
			rdParam.put("sync_id",maxId);
			
			boolean flagDaoCommit = false;
			//删除一天前已经成功同步的记录
			if ( ! StringUtils.isBlank(maxId)) {
				Calendar c = Calendar.getInstance();
				c.add(Calendar.DATE, -1);	
				String curTime = CommonUtil.dateToStr(c.getTime());
				
				sql = "delete from biz_register_sync where sync_id<'"+maxId+"' and sync_status='success' and insert_time<'"+curTime+"'";
				dao.update(sql,rdParam);
				
				sql = "delete from biz_clinic_sync where sync_id<'"+maxId+"' and sync_status='success' and insert_time<'"+curTime+"'";
				dao.update(sql,rdParam);
				flagDaoCommit = true;
			}
			
			//总记录数
			int recordCount = 0;
			//出错次数
			int errorCount = 0;

							
			//处理
			sql = "select count(*) as cnt from "+sourceTableName+" where sync_id>?";
			int count = dao.queryForInt(sql,rdParam);
			
//			boolean bTest = true;
//			if (count>0 && bTest) {
//				count = 1;
//				pageSize = 1;
//			}
			
			for (int m = 0;m<count;m += pageSize) {
				sql = "select * from "+sourceTableName+" where sync_id>? order by sync_id";
				RecordSet rs = dao.query(sql,rdParam,1,pageSize);
				
				if (rs.size() > 0) {
					
					//重新确定最大ID
					maxId = rs.getRecord(rs.size() -1).getString("sync_id");
					
//					//流水
//					String sqlJournal = "select * from "+sourceTableName+" where sync_id>? and sync_id<=:'"+maxId+"' order by sync_id";
//					RecordSet rsJournal = dao.query(sqlJournal,rdParam,1,pageSize);//因为可能生成正负流水，流水记录集扩展一倍
//					
					rdParam.put("sync_id",maxId);
					recordCount += rs.size();
					
					
					
					//连续错误次数s
					int errorSeq = 0;
					
					for (int i = 0;i<rs.size();i++) {
						Record rd = rs.getRecord(i);
						
						//本记录实际来源的实际表名
						String realSourceTableName = "biz_clinic_sync";
						if ("register".equalsIgnoreCase(rd.getString("data_source"))) {
							realSourceTableName = "biz_register_sync";
						}
						try {
							if (rd.isEmpty("org_id")) {
								rd.put("org_id","unknow");
							}
							if (rd.isEmpty("org_name")) {
								rd.put("org_name","unknow");
							}
							
							longChuanTranslate(rd);
							
							/**
							 * 
从eh_user取CARD_TYPE,CARD_NO,ADDRESS,SEX,ID 关联关系 中间表.user_id=用户表.id
从biz_medical_card取user_id,user_name 中间表.user_id=绑卡表.user_id
							 */
							//从用户表获取数据
							Record rdUser = dao.selectOne(sqlEhUser,rd);
							if (rdUser != null) {
								rd.put(rdUser.gField("card_type"));
								rd.put(rdUser.gField("CARD_NO"));
								rd.put(rdUser.gField("ADDRESS"));
								rd.put(rdUser.gField("SEX"));
								
							}
							else {
								logger.error("从数据库eh_user无法获得指定的用户信息，user_id="+rd.getString("user_id"));
							}
							
							
							//从诊疗卡表获取用户数据
						
							Record rdMedicalCard = dao.selectOne(sqlMedicalCard,rd);
							if (rdMedicalCard != null) {
								rd.put(rdMedicalCard.gField("name"));
								
							}
							else {
								logger.error("从数据库biz_medical_card无法获得指定的诊疗卡信息，user_id="+rd.getString("user_id"));
							}
							
							boolean addJournalFlag = false;
							if (rd.gIntValue("add_flag") == 1) {//新增
								rd.put("id",IDUtils.getUUID32());
								rd.put("data_source","import_pro");
								
								this.insertData(targetTableName, rd);
								addJournalFlag = true;
							}
							else if (rd.gIntValue("update_flag") == 1) {//更新
																	
								this.updateData(targetTableName, rd,"order_no","id");
								addJournalFlag = true;
							}
							else {
								logger.error(sourceTableName+"无效的同步数据："+JsonUtils.toJsonString(rd	));
							}
							
							//流水数据
							if (addJournalFlag) {
								RecordSet rsJournal = null;
								try {
									sql = "select * from v_pay_payment_journal_sync where sync_id=? and add_flag=1";
									rsJournal = dao.query(sql,rd);
									if (rsJournal != null && rsJournal.size() > 0) {
										this.insertDataBatch("pay_payment_journal", rsJournal);
									}
									
								}
								catch (Exception ex) {
									logger.error("新增流水失败:\n"+rsJournal,ex);
								}
							}
							
							errorSeq = 0;
						} catch (Exception e) {
							errorCount++;
							errorSeq++;
							logger.error(sourceTableName+"同步订单数据错误，数据为："+JsonUtils.toJsonString(rd));
							logger.error(ErrorCodeDefine.getStackTrace(e));
							
							//记录错误信息
							try {
								String errorInfo = ErrorCodeDefine.getStackTrace(e);
								rd.put("sync_info",errorInfo);
								dao.update("update "+realSourceTableName+" set sync_status='fail',sync_info=? where sync_id=?",rd);
							}
							catch (Exception e2) {
								
								logger.error(realSourceTableName+"同步错误，无法更新源数据标志位，数据为："+JsonUtils.toJsonString(rd));
								logger.error(ErrorCodeDefine.getStackTrace(e2));
							}
							
						}
						
					}
					
					//标记最大ID值
					this.update("update sys_sync_id set sync_max_id=:sync_id where sync_table=?",rdParam);
					this.commit();
					
					// 错误数据太多，退出同步
					if (errorCount == recordCount) {
						logger.error(sourceTableName + "同步全部出错，本次同步结束");
						break;
					}
					
					
				}
			}
			
			if (errorCount > 0 || flagDaoCommit) {
				dao.commit();
			}
			
			
			if (recordCount > 0) {
				if (errorCount == 0) {
					logger.info(sourceTableName+"数据同步成功，耗时："+(System.currentTimeMillis() - start)+"毫秒,处理记录总数"+recordCount);
				}
				else {
					logger.error(sourceTableName+"数据同步完成，但存在异常数据，耗时："+(System.currentTimeMillis() - start)+"毫秒,处理记录总数"+recordCount+",其中错误记录数："+errorCount);
				}
			}
			else {
				logger.info(sourceTableName+"没有需要同步的记录");
			}
			
		}
		catch(Exception ex) {
			logger.error(sourceTableName+"数据同步异常,耗时："+(System.currentTimeMillis() - start)+"毫秒\n"+ErrorCodeDefine.getStackTrace(ex));

		}
		finally {
			try {
				if (dao != null) {
					dao.releaseCon();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	
	}
	
	/**
	 * 龙川县中医院服务号 转换成 龙川县中医院
	 * @param rd
	 */
	public static void longChuanTranslate(Record rd) {
		if (rd.getString("org_name","").equals("龙川县中医院服务号")) {
			rd.put("org_code","lczyy");
			rd.put("org_name","龙川县中医院");
			rd.put("org_id","cce904aad814403d860d30fb91900a28");
		}
	}
	
	
	
}
